import pymysql
import numpy as np
from clothing_data_model import ClothingItem, UserProfile, Outfit


class DatabaseConnector:
    def __init__(self, host, user, password, database):
        """初始化数据库连接器"""
        self.connection_params = {
            'host': host,
            'user': user,
            'password': password,
            'database': database,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }

    def connect(self):
        """创建数据库连接"""
        return pymysql.connect(**self.connection_params)

    def load_clothing_items(self, user_id=None):
        """从数据库加载服装项，可选择按用户ID过滤"""
        items = []

        try:
            with self.connect() as connection:
                with connection.cursor() as cursor:
                    # 查询clothes表中的服装项，可选择按用户ID过滤
                    if user_id:
                        sql = """
                        SELECT clothes_id, category, subcategory, color, material, season, fit
                        FROM clothes
                        WHERE user_id = %s
                        """
                        cursor.execute(sql, (user_id,))
                    else:
                        sql = """
                        SELECT clothes_id, category, subcategory, color, material, season, fit
                        FROM clothes
                        """
                        cursor.execute(sql)
                    results = cursor.fetchall()

                    for row in results:
                        item = ClothingItem(
                            clothes_id=row['clothes_id'],
                            category=row['category'],
                            subcategory=row['subcategory'],
                            color=row['color'],
                            material=row['material'],
                            season=row['season'],
                            fit=row['fit']
                        )
                        items.append(item)
        except Exception as e:
            print(f"数据库错误: {e}")

        return items

    def load_user_profile(self, user_id):
        """从数据库加载用户档案"""
        user_profile = UserProfile(user_id)

        try:
            with self.connect() as connection:
                with connection.cursor() as cursor:
                    # 加载用户偏好
                    sql = """
                    SELECT attribute_type, attribute_value, preference_score
                    FROM user_preferences
                    WHERE user_id = %s
                    """
                    cursor.execute(sql, (user_id,))
                    preferences = cursor.fetchall()

                    for pref in preferences:
                        attr_type = pref['attribute_type']
                        attr_value = pref['attribute_value']
                        score = pref['preference_score']

                        if attr_type in user_profile.preferences:
                            user_profile.preferences[attr_type][attr_value] = score
                        elif attr_type == 'style':
                            user_profile.style_preferences[attr_value] = score

                    # 加载交互历史
                    sql = """
                    SELECT outfit_id, rating
                    FROM outfit_feedback
                    WHERE user_id = %s
                    ORDER BY feedback_time DESC
                    LIMIT 20
                    """
                    cursor.execute(sql, (user_id,))
                    interactions = cursor.fetchall()

                    for interaction in interactions:
                        outfit_id = interaction['outfit_id']
                        rating = interaction['rating']

                        # 获取搭配中的衣服
                        outfit_items = self.get_outfit_items(outfit_id)
                        if outfit_items:
                            outfit = Outfit(outfit_id, outfit_items)
                            outfit.user_id = user_id  # 设置用户ID
                            user_profile.interaction_history.append((outfit, rating))

                            if rating > 3:
                                user_profile.liked_outfits.append(outfit)
                                user_profile.liked_items.extend(outfit_items)
        except Exception as e:
            print(f"加载用户档案时出错: {e}")

        return user_profile

    def get_outfit_items(self, outfit_id):
        """获取搭配中的所有衣服"""
        items = []

        try:
            with self.connect() as connection:
                with connection.cursor() as cursor:
                    sql = """
                    SELECT c.clothes_id, c.category, c.subcategory, c.color, c.material, c.season, c.fit
                    FROM outfit_items oi
                    JOIN clothes c ON oi.clothes_id = c.clothes_id
                    WHERE oi.outfit_id = %s
                    """
                    cursor.execute(sql, (outfit_id,))
                    results = cursor.fetchall()

                    for row in results:
                        item = ClothingItem(
                            clothes_id=row['clothes_id'],
                            category=row['category'],
                            subcategory=row['subcategory'],
                            color=row['color'],
                            material=row['material'],
                            season=row['season'],
                            fit=row['fit']
                        )
                        items.append(item)
        except Exception as e:
            print(f"获取搭配衣服时出错: {e}")

        return items

    def save_outfit(self, outfit):
        """保存搭配到数据库"""
        try:
            # 确保outfit有user_id
            if not hasattr(outfit, 'user_id') or outfit.user_id is None:
                raise ValueError("保存搭配时缺少user_id")

            with self.connect() as connection:
                with connection.cursor() as cursor:
                    # 检查搭配是否已存在
                    sql = "SELECT outfit_id FROM outfits WHERE outfit_id = %s"
                    cursor.execute(sql, (outfit.outfit_id,))
                    if cursor.fetchone():
                        # 如果已存在，先删除旧的搭配项
                        sql = "DELETE FROM outfit_items WHERE outfit_id = %s"
                        cursor.execute(sql, (outfit.outfit_id,))
                    else:
                        # 如果不存在，创建新搭配
                        sql = "INSERT INTO outfits (outfit_id, user_id, created_at) VALUES (%s, %s, NOW())"
                        cursor.execute(sql, (outfit.outfit_id, outfit.user_id))

                    # 保存搭配中的衣服
                    for item in outfit.items:
                        sql = """
                        INSERT INTO outfit_items (outfit_id, clothes_id)
                        VALUES (%s, %s)
                        """
                        cursor.execute(sql, (outfit.outfit_id, item.clothes_id))

                    connection.commit()
        except Exception as e:
            print(f"保存搭配时出错: {e}")
            raise  # 重新抛出异常，以便调用者知道出错了

    def save_user_feedback(self, user_id, outfit, rating, style=None):
        """保存用户对搭配的反馈"""
        try:
            with self.connect() as connection:
                with connection.cursor() as cursor:
                    # 确保outfit有user_id
                    outfit.user_id = user_id

                    # 保存搭配（如果尚未保存）
                    self.save_outfit(outfit)

                    # 保存搭配反馈
                    sql = """
                    INSERT INTO outfit_feedback (user_id, outfit_id, rating, feedback_time)
                    VALUES (%s, %s, %s, NOW())
                    """
                    cursor.execute(sql, (user_id, outfit.outfit_id, rating))

                    # 更新用户偏好
                    for item in outfit.items:
                        # 更新类别偏好
                        self._update_preference(cursor, user_id, 'category', item.category, rating)
                        # 更新子类别偏好
                        self._update_preference(cursor, user_id, 'subcategory', item.subcategory, rating)
                        # 更新颜色偏好
                        self._update_preference(cursor, user_id, 'color', item.color, rating)
                        # 更新材质偏好
                        self._update_preference(cursor, user_id, 'material', item.material, rating)
                        # 更新季节偏好
                        self._update_preference(cursor, user_id, 'season', item.season, rating)
                        # 更新版型偏好
                        self._update_preference(cursor, user_id, 'fit', item.fit, rating)

                    # 如果提供了风格，更新风格偏好
                    if style:
                        self._update_preference(cursor, user_id, 'style', style, rating)

                    connection.commit()
        except Exception as e:
            print(f"保存用户反馈时出错: {e}")

    def _update_preference(self, cursor, user_id, attr_type, attr_value, rating):
        """更新用户对特定属性的偏好"""
        # 将评分 (1-5) 转换为偏好调整值 (-1 到 1)
        preference_adjustment = (rating - 3) / 2

        # 检查偏好是否已存在
        sql = """
        SELECT preference_score FROM user_preferences
        WHERE user_id = %s AND attribute_type = %s AND attribute_value = %s
        """
        cursor.execute(sql, (user_id, attr_type, attr_value))
        result = cursor.fetchone()

        if result:
            # 更新现有偏好
            current_score = result['preference_score']
            new_score = current_score + preference_adjustment

            # 设置偏好上限
            MAX_PREFERENCE = 2.0
            new_score = min(MAX_PREFERENCE, new_score)

            sql = """
            UPDATE user_preferences
            SET preference_score = %s, last_updated = NOW()
            WHERE user_id = %s AND attribute_type = %s AND attribute_value = %s
            """
            cursor.execute(sql, (new_score, user_id, attr_type, attr_value))
        else:
            # 创建新偏好
            sql = """
            INSERT INTO user_preferences (user_id, attribute_type, attribute_value, preference_score, last_updated)
            VALUES (%s, %s, %s, %s, NOW())
            """
            cursor.execute(sql, (user_id, attr_type, attr_value, preference_adjustment))

